<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class StatisticsAttendDept extends AdminController {

	function __construct() {
		parent::__construct();
		$this-> load->model('Attend_model');
	}

	function Index(){
		$this->load->view('admin/statisticsAttendDeptList');
	}

	function getDatas($params = array(), $isExport = false){
		$connar = array();
		if (!$isExport) {
			$params = $_POST;
			$connar = $this -> Attend_model -> pickPages($connar, $params);
		}
		// $connar['items']='listen_id,db_admin.dept_name';
		// $connar['where']=$this->sqlEscape('db_attend.status=??',array(AttendStatus::Submit[XPHEnum::Value]));
		// $connar['join']=array('db_admin','db_admin.id=db_attend.listen_id');
		// $connar['groupby']='db_admin.dept_name';
		$where=$this->sqlEscape('where status=??',array(AttendStatus::Submit[XPHEnum::Value]));
		$role=explode(',',getSess()['sub_role_id']);
		array_push($role,getSess()['role_id']);		
		if(in_array(WebRole::Manage[XPHEnum::Value], $role)){
			// 记得解开
			// $connar['where'].=$this->sqlEscape(' and admin_type=??',array(AdminType::Auth[XPHEnum::Value]));
		}else if(in_array(WebRole::UnitSupervisorGroupLeader[XPHEnum::Value], $role)){
			//$where.=$this->sqlEscape(' and dept_name=??',array(getSess()['dept_name']));
			 //查询该用户的开课单位信息
        	$connar5['items']='distinct(course_units)';
			$connar5['where']=$this->sqlEscape('teacher_code=??',array(getSess()['usercode']));
			$dept_name_1=$this->Attend_model->get_single_full($connar5);
			$where.=$this->sqlEscape(' and course_units=??',array($dept_name_1['course_units']));

		}else if(in_array(WebRole::UnitSupervisor[XPHEnum::Value], $role) || in_array(WebRole::SchoolSupervisor[XPHEnum::Value], $role) || in_array(WebRole::OutsideSupervisor[XPHEnum::Value], $role)){
			$where.=$this->sqlEscape(' and teacher_code=??',array(getSess()['usercode']));
		}
		
		// if(!$isExport){
		// 	if(isset($params['pageNo']) && isset($params['pageSize'])){			
		// 		$pageNo=$_POST['pageNo']-1;
		// 		$pageSize=$_POST['pageSize'];
		// 	}else{
		// 		$pageNo='0';
		// 		$pageSize='20';
		// 	}
		// }
		if (isset($params['start_time']) && isset($params['end_time'])) {
			$where.=' and db_attend.listen_time>='.strtotime($params['start_time']).' and db_attend.listen_time<='.strtotime($params['end_time'].'23:59:59');
		}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
			$where.=' and db_attend.listen_time>='.strtotime($_POST['start_time']).' and db_attend.listen_time<='.strtotime($_POST['end_time'].'23:59:59');
		}
		//if (!$isExport) {	
			// $data = $this -> Attend_model -> get_page_list($connar);
			// $data['ret']=$this -> Attend_model->query('select * from(select group_concat(distinct listen_id) as listen_id,db_admin.dept_name from db_attend left join db_admin on db_admin.id=db_attend.listen_id '.$where.' group by dept_name) attend  group by field(dept_name,"电气电子工程分院","机械工程分院","建筑工程分院","设计与艺术分院","财经分院","交通分院","商贸分院","鉴湖分院","人文社科部") limit '.$pageSize*$pageNo.','.$pageSize);
			// $count=$this -> Attend_model -> query('select count(*) count from (select * from(select group_concat(listen_id) as listen_id,db_admin.dept_name from db_attend left join db_admin on db_admin.id=db_attend.listen_id '.$where.'  group by dept_name) attend)a limit '.$pageSize*$pageNo.','.$pageSize);
			// $data['count']=$count[0]['count'];
			// $data['totalPage']=ceil($data['count']/$pageSize);

		//} else {
			// $data['ret']=$this -> Attend_model->query('select * from(select group_concat(distinct listen_id) as listen_id,db_admin.dept_name from db_attend left join db_admin on db_admin.id=db_attend.listen_id '.$where.' group by dept_name) attend group by field(dept_name,"电气电子工程分院","机械工程分院","建筑工程分院","设计与艺术分院","财经分院","交通分院","商贸分院","鉴湖分院","人文社科部")');			
		//}
		// $connar['items']="group_concat(DISTINCT id) attend_id,db_attend.course_units";
		// $connar['groupby']="course_units";
		// $connar['orderby']='field(dept_name,"电气电子工程分院","机械工程分院","建筑工程分院","设计与艺术分院","财经分院","交通分院","商贸分院","鉴湖分院","人文社科部")';

		if (!$isExport) {
			$data['ret'] = $this -> Attend_model -> query('select group_concat(DISTINCT id) attend_id,db_attend.course_units from db_attend '.$where.' group by course_units order by field(dept_name,"电气电子工程分院","机械工程分院","建筑工程分院","设计与艺术分院","财经分院","交通分院","商贸分院","鉴湖分院","人文社科部")');
			$count=$this->Attend_model->query('select count(*) count from (select group_concat(DISTINCT id) attend_id,db_attend.course_units from db_attend '.$where.' group by course_units order by field(dept_name,"电气电子工程分院","机械工程分院","建筑工程分院","设计与艺术分院","财经分院","交通分院","商贸分院","鉴湖分院","人文社科部"))a');
			$data['count']=$count[0]['count'];
			$data["totalPage"] = ceil($count[0]["count"] / $params['pageSize']);
			$data['pageNo'] = $params['pageNo'];
		} else {
			$data['ret'] = $this -> Attend_model -> query('select group_concat(DISTINCT id) attend_id,db_attend.course_units from db_attend '.$where.' group by course_units order by field(dept_name,"电气电子工程分院","机械工程分院","建筑工程分院","设计与艺术分院","财经分院","交通分院","商贸分院","鉴湖分院","人文社科部")');
		}
		

			$max_all=-1;
			$min_all=999; 
		foreach ($data['ret'] as $k => $v) {
			// 督导听课人数
			$connar2['items']='count(DISTINCT teacher_name) attend_num';
			$connar2['where']='id in('.$v['attend_id'].') and status='.AttendStatus::Submit[XPHEnum::Value].' and type='.AttendType::Unit[XPHEnum::Value];
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar2['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar2['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}
			$num=$this->Attend_model->get_single_full($connar2);
			$data['ret'][$k]['attend_num']=$num['attend_num'];

			// 督导听课次数
			$connar1['items']='COUNT(*) attend_count';
			$connar1['where']='id in('.$v['attend_id'].') and status='.AttendStatus::Submit[XPHEnum::Value].' and type='.AttendType::Unit[XPHEnum::Value];
			$connar1['groupby']='';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}
			$count=$this->Attend_model->get_single_full($connar1);
			$data['ret'][$k]['attend_count']=$count['attend_count'];
			

			// 校督导听课人数
			$connar2['items']='count(DISTINCT teacher_name) attend_num';
			$connar2['where']='id in('.$v['attend_id'].') and status='.AttendStatus::Submit[XPHEnum::Value].' and type='.AttendType::School[XPHEnum::Value];
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar2['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar2['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}
			$num=$this->Attend_model->get_single_full($connar2);
			$data['ret'][$k]['attend_num_school']=$num['attend_num'];

			// 校督导听课次数
			$connar1['items']='COUNT(*) attend_count';
			$connar1['where']='id in('.$v['attend_id'].') and status='.AttendStatus::Submit[XPHEnum::Value].' and type='.AttendType::School[XPHEnum::Value];
			$connar1['groupby']='';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}
			$count=$this->Attend_model->get_single_full($connar1);
			$data['ret'][$k]['attend_count_school']=$count['attend_count'];


			// 外聘督导听课人数
			$connar2['items']='count(DISTINCT teacher_name) attend_num';
			$connar2['where']='id in('.$v['attend_id'].') and status='.AttendStatus::Submit[XPHEnum::Value].' and type='.AttendType::Outside[XPHEnum::Value];
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar2['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar2['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}
			$num=$this->Attend_model->get_single_full($connar2);
			$data['ret'][$k]['attend_num_outside']=$num['attend_num'];

			// 外聘督导听课次数
			$connar1['items']='COUNT(*) attend_count';
			$connar1['where']='id in('.$v['attend_id'].') and status='.AttendStatus::Submit[XPHEnum::Value].' and type='.AttendType::Outside[XPHEnum::Value];
			$connar1['groupby']='';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}
			$count=$this->Attend_model->get_single_full($connar1);
			$data['ret'][$k]['attend_count_outside']=$count['attend_count'];

			// 总平均分			
			$connar1['items']='avg(score)avg';
			$connar1['where']='id in('.$v['attend_id'].') and status='.AttendStatus::Submit[XPHEnum::Value];		
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}
			$avg=$this->Attend_model->get_single_full($connar1);
			$data['ret'][$k]['avg_total_score']=sprintf("%.2f",$avg['avg']);

				//校督导听课平均分	
			$connar1['items']='avg(score)avg';
			$connar1['where']='id in('.$v['attend_id'].') and type='.AttendType::School[XPHEnum::Value].' and status='.AttendStatus::Submit[XPHEnum::Value];		
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}
			$avg=$this->Attend_model->get_single_full($connar1);
			$data['ret'][$k]['avg_school_score']=sprintf("%.2f",$avg['avg']);

				//二级督导听课平均分	
			$connar1['items']='avg(score)avg';
			$connar1['where']='id in('.$v['attend_id'].') and type='.AttendType::Unit[XPHEnum::Value].' and status='.AttendStatus::Submit[XPHEnum::Value];		
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}
			$avg=$this->Attend_model->get_single_full($connar1);
			$data['ret'][$k]['avg_unit_score']=sprintf("%.2f",$avg['avg']);


				//外聘督导听课平均分	
			$connar1['items']='avg(score)avg';
			$connar1['where']='id in('.$v['attend_id'].') and type='.AttendType::Outside[XPHEnum::Value].' and status='.AttendStatus::Submit[XPHEnum::Value];		
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			}
			$avg=$this->Attend_model->get_single_full($connar1);
			$data['ret'][$k]['avg_outside_score']=sprintf("%.2f",$avg['avg']);

			//总分最高分			
			$where=" and id in(".$v['attend_id'].")";
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$where.=$this->sqlEscape(' and db_attend.listen_time>??  and db_attend.listen_time<??',array(strtotime($params['start_time'].' 00:00:00'),strtotime($_POST['end_time']." 23:59:59")));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$where.=' and db_attend.listen_time>'.strtotime($_POST['start_time'].' 00:00:00').' and db_attend.listen_time<'.strtotime($_POST['end_time']." 23:59:59");
			}
			$sql=$this->sqlEscape("select * from db_attend where score=(SELECT max(score) from db_attend where status=?? ".$where." ) and status=?? ".$where." group by teacher_name",array(AttendStatus::Submit[XPHEnum::Value],AttendStatus::Submit[XPHEnum::Value]));
			$max=$this->Attend_model->query($sql);			
			$max_score=$max[0]['score'];
			$max_teacher_name=implode(',', array_column($max,'teacher_name'));
			$data['ret'][$k]['max_score']=sprintf("%.2f",$max_score).'('.$max_teacher_name.')';

			//总分最低分
			$where=" and id in(".$v['attend_id'].")";
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$where.=$this->sqlEscape(' and db_attend.listen_time>??  and db_attend.listen_time<??',array(strtotime($params['start_time'].' 00:00:00'),strtotime($_POST['end_time']." 23:59:59")));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$where.=' and db_attend.listen_time>'.strtotime($_POST['start_time'].' 00:00:00').' and db_attend.listen_time<'.strtotime($_POST['end_time']." 23:59:59");
			}
			$sql=$this->sqlEscape("select * from db_attend where score=(SELECT min(score) from db_attend where status=?? ".$where." ) and status=?? ".$where." group by teacher_name",array(AttendStatus::Submit[XPHEnum::Value],AttendStatus::Submit[XPHEnum::Value]));
			$min=$this->Attend_model->query($sql);			
			$min_score=$min[0]['score'];
			$min_teacher_name=implode(',', array_column($min,'teacher_name'));
			$data['ret'][$k]['min_score']=sprintf("%.2f",$min_score).'('.$min_teacher_name.')';

			
			// // 教学水平平均分			
			// $connar1['items']='avg(teaching_level_score)avg';
			// $connar1['where']='id in('.$v['attend_id'].') and status='.AttendStatus::Submit[XPHEnum::Value];		
			// if (isset($params['start_time']) && isset($params['end_time'])) {
			// 	$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			// }else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
			// 	$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			// }
			// $avg=$this->Attend_model->get_single_full($connar1);
			// $data['ret'][$k]['avg_level']=sprintf("%.2f",$avg['avg']);

			// // 教学效果平均分			
			// $connar1['items']='avg(teaching_effect_score)avg';
			// $connar1['where']='id in('.$v['attend_id'].') and status='.AttendStatus::Submit[XPHEnum::Value];		
			// if (isset($params['start_time']) && isset($params['end_time'])) {
			// 	$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			// }else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
			// 	$connar1['where'].=$this->sqlEscape(" and listen_time>=?? and listen_time<=??",array(strtotime($_POST['start_time']),strtotime($_POST['end_time'].'23:59:59')));
			// }
			// $avg=$this->Attend_model->get_single_full($connar1);
			// $data['ret'][$k]['avg_effect']=sprintf("%.2f",$avg['avg']);

			// // 教学水平90分(含)以上
			// $connar1['items']="count(*) as count";
			// $connar1['where']=$this->sqlEscape('teaching_level_score>=90 and status=??',array(AttendStatus::Submit[XPHEnum::Value]));
			// $connar1['where'].=" and id in(".$v['attend_id'].")";
			// if (isset($params['start_time']) && isset($params['end_time'])) {
			// 	$connar1['where'].=$this->sqlEscape(' and db_attend.listen_time>=??  and db_attend.listen_time<=??',array(strtotime($params['start_time'].' 00:00:00'),strtotime($_POST['end_time']." 23:59:59")));
			// }else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
			// 	$connar1['where'].=' and db_attend.listen_time>='.strtotime($_POST['start_time'].' 00:00:00').' and db_attend.listen_time<='.strtotime($_POST['end_time']." 23:59:59");
			// }
			// $abovenine=$this->Attend_model->get_single_full($connar1);
			// // 教学水平80以下
			// $connar1['items']="count(*) as count";
			// $connar1['where']=$this->sqlEscape('teaching_level_score<80 and status=??',array(AttendStatus::Submit[XPHEnum::Value]));
			// $connar1['where'].=" and id in(".$v['attend_id'].")";
			// if (isset($params['start_time']) && isset($params['end_time'])) {
			// 	$connar1['where'].=$this->sqlEscape(' and db_attend.listen_time>=??  and db_attend.listen_time<=??',array(strtotime($params['start_time'].' 00:00:00'),strtotime($_POST['end_time']." 23:59:59")));
			// }else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
			// 	$connar1['where'].=' and db_attend.listen_time>='.strtotime($_POST['start_time'].' 00:00:00').' and db_attend.listen_time<='.strtotime($_POST['end_time']." 23:59:59");
			// }
			// $aboveeight=$this->Attend_model->get_single_full($connar1);

			// $data['ret'][$k]['abovenine_level']=$abovenine['count'];
			// $data['ret'][$k]['aboveeight_level']=$aboveeight['count'];

			// // 教学效果90分(含)以上
			// $connar1['items']="count(*) as count";
			// $connar1['where']=$this->sqlEscape('teaching_effect_score>=90 and status=??',array(AttendStatus::Submit[XPHEnum::Value]));
			// $connar1['where'].=" and id in(".$v['attend_id'].")";
			// if (isset($params['start_time']) && isset($params['end_time'])) {
			// 	$connar1['where'].=$this->sqlEscape(' and db_attend.listen_time>=??  and db_attend.listen_time<=??',array(strtotime($params['start_time'].' 00:00:00'),strtotime($_POST['end_time']." 23:59:59")));
			// }else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
			// 	$connar1['where'].=' and db_attend.listen_time>='.strtotime($_POST['start_time'].' 00:00:00').' and db_attend.listen_time<='.strtotime($_POST['end_time']." 23:59:59");
			// }
			// $abovenine=$this->Attend_model->get_single_full($connar1);
			// // 教学效果80以下
			// $connar1['items']="count(*) as count";
			// $connar1['where']=$this->sqlEscape('teaching_effect_score<80 and status=??',array(AttendStatus::Submit[XPHEnum::Value]));
			// $connar1['where'].=" and id in(".$v['attend_id'].")";
			// if (isset($params['start_time']) && isset($params['end_time'])) {
			// 	$connar1['where'].=$this->sqlEscape(' and db_attend.listen_time>=??  and db_attend.listen_time<=??',array(strtotime($params['start_time'].' 00:00:00'),strtotime($_POST['end_time']." 23:59:59")));
			// }else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
			// 	$connar1['where'].=' and db_attend.listen_time>='.strtotime($_POST['start_time'].' 00:00:00').' and db_attend.listen_time<='.strtotime($_POST['end_time']." 23:59:59");
			// }
			// $aboveeight=$this->Attend_model->get_single_full($connar1);

			// $data['ret'][$k]['abovenine_effect']=$abovenine['count'];
			// $data['ret'][$k]['aboveeight_effect']=$aboveeight['count'];

			// // 教学水平最高分			
			// $where=" and id in(".$v['attend_id'].")";
			// if (isset($params['start_time']) && isset($params['end_time'])) {
			// 	$where.=$this->sqlEscape(' and db_attend.listen_time>??  and db_attend.listen_time<??',array(strtotime($params['start_time'].' 00:00:00'),strtotime($_POST['end_time']." 23:59:59")));
			// }else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
			// 	$where.=' and db_attend.listen_time>'.strtotime($_POST['start_time'].' 00:00:00').' and db_attend.listen_time<'.strtotime($_POST['end_time']." 23:59:59");
			// }
			// $sql=$this->sqlEscape("select * from db_attend where teaching_level_score=(SELECT max(teaching_level_score) from db_attend where status=?? ".$where." ) and status=?? ".$where." group by teacher_name",array(AttendStatus::Submit[XPHEnum::Value],AttendStatus::Submit[XPHEnum::Value]));
			// $max=$this->Attend_model->query($sql);			
			// $max_score=$max[0]['teaching_level_score'];
			// $max_teacher_name=implode(',', array_column($max,'teacher_name'));
			// $data['ret'][$k]['max_level']=sprintf("%.2f",$max_score).'('.$max_teacher_name.')';
			// //教学水平 最低分
			// $where=" and id in(".$v['attend_id'].")";
			// if (isset($params['start_time']) && isset($params['end_time'])) {
			// 	$where.=$this->sqlEscape(' and db_attend.listen_time>??  and db_attend.listen_time<??',array(strtotime($params['start_time'].' 00:00:00'),strtotime($_POST['end_time']." 23:59:59")));
			// }else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
			// 	$where.=' and db_attend.listen_time>'.strtotime($_POST['start_time'].' 00:00:00').' and db_attend.listen_time<'.strtotime($_POST['end_time']." 23:59:59");
			// }
			// $sql=$this->sqlEscape("select * from db_attend where teaching_level_score=(SELECT min(teaching_level_score) from db_attend where status=?? ".$where." ) and status=?? ".$where." group by teacher_name",array(AttendStatus::Submit[XPHEnum::Value],AttendStatus::Submit[XPHEnum::Value]));
			// $min=$this->Attend_model->query($sql);			
			// $min_score=$min[0]['teaching_level_score'];
			// $min_teacher_name=implode(',', array_column($min,'teacher_name'));
			// $data['ret'][$k]['min_level']=sprintf("%.2f",$min_score).'('.$min_teacher_name.')';

			// // 教学效果高分			
			// $where=" and id in(".$v['attend_id'].")";
			// if (isset($params['start_time']) && isset($params['end_time'])) {
			// 	$where.=$this->sqlEscape(' and db_attend.listen_time>??  and db_attend.listen_time<??',array(strtotime($params['start_time'].' 00:00:00'),strtotime($_POST['end_time']." 23:59:59")));
			// }else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
			// 	$where.=' and db_attend.listen_time>'.strtotime($_POST['start_time'].' 00:00:00').' and db_attend.listen_time<'.strtotime($_POST['end_time']." 23:59:59");
			// }
			// $sql=$this->sqlEscape("select * from db_attend where teaching_effect_score=(SELECT max(teaching_effect_score) from db_attend where status=?? ".$where." ) and status=?? ".$where." group by teacher_name",array(AttendStatus::Submit[XPHEnum::Value],AttendStatus::Submit[XPHEnum::Value]));
			// $max=$this->Attend_model->query($sql);			
			// $max_score=$max[0]['teaching_effect_score'];
			// $max_teacher_name=implode(',', array_column($max,'teacher_name'));
			// $data['ret'][$k]['max_effect']=sprintf("%.2f",$max_score).'('.$max_teacher_name.')';
			// //教学效果最低分
			// $where=" and id in(".$v['attend_id'].")";
			// if (isset($params['start_time']) && isset($params['end_time'])) {
			// 	$where.=$this->sqlEscape(' and db_attend.listen_time>??  and db_attend.listen_time<??',array(strtotime($params['start_time'].' 00:00:00'),strtotime($_POST['end_time']." 23:59:59")));
			// }else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
			// 	$where.=' and db_attend.listen_time>'.strtotime($_POST['start_time'].' 00:00:00').' and db_attend.listen_time<'.strtotime($_POST['end_time']." 23:59:59");
			// }
			// $sql=$this->sqlEscape("select * from db_attend where teaching_effect_score=(SELECT min(teaching_effect_score) from db_attend where status=?? ".$where." ) and status=?? ".$where." group by teacher_name",array(AttendStatus::Submit[XPHEnum::Value],AttendStatus::Submit[XPHEnum::Value]));
			// $min=$this->Attend_model->query($sql);	
			// $min_score=$min[0]['teaching_effect_score'];
			// $min_teacher_name=implode(',', array_column($min,'teacher_name'));
			// $data['ret'][$k]['min_effect']=sprintf("%.2f",$min_score).'('.$min_teacher_name.')';
			
		}
		if (!$isExport) {
			echo json_encode($data);
		} else {
			foreach ($data['ret'] as $k => $v) {
				$data['ret'][$k]['attend_id']=$k+1;
			}
			// 合计
			$attend_num_unit=0;
			$attend_count_unit=0;
			$attend_num_school=0;
			$attend_count_school=0;
			$avg_level_count=0;
			$avg_effect_count=0;
			$abovenine_level_count=0;
			$aboveeight_level_count=0;
			$abovenine_effect_count=0;
			$aboveeight_effect_count=0;
			$max_count=0;
			$min_count=0;
			// $connar1['items']='group_concat(teacher_name) teacher_name';
			// $connar1['where']='id in('.$maxid_all.') and status='.AttendStatus::Submit[XPHEnum::Value];
			// $max_all_name=$this->Attend_model->get_single_full($connar1);
			// $max_all_name=sprintf("%.2f",$max_all).'('.$max_all_name['teacher_name'].')';
			// $connar1['items']='group_concat(teacher_name) teacher_name';
			// $connar1['where']='id in('.$minid_all.') and status='.AttendStatus::Submit[XPHEnum::Value];
			// $min_all_name=$this->Attend_model->get_single_full($connar1);
			// $min_all_name=sprintf("%.2f",$min_all).'('.$min_all_name['teacher_name'].')';
			foreach ($data['ret'] as $k => $v) {
				$attend_num_unit+=$v['attend_num'];
				$attend_count_unit+=$v['attend_count'];
				$attend_num_school+=$v['attend_num_school'];
				$attend_count_school+=$v['attend_count_school'];
				$avg_level_count+=$v['avg_level'];
				$avg_effect_count+=$v['avg_effect'];
				$abovenine_level_count+=$v['abovenine_level'];
				$aboveeight_level_count+=$v['aboveeight_level'];
				$abovenine_effect_count+=$v['abovenine_effect'];
				$aboveeight_effect_count+=$v['aboveeight_effect'];
				// $max_count+=$v['max'];
				// $min_count+=$v['min'];
			}
			if($avg_level_count==0 ||count($data['ret'])==0 ){
				$avg_level_count=0;
			}else{
				$avg_level_count=sprintf("%.2f",($avg_level_count)/count($data['ret']));
			}

			if($avg_effect_count==0 ||count($data['ret'])==0 ){
				$avg_effect_count=0;
			}else{
				$avg_effect_count=sprintf("%.2f",($avg_effect_count)/count($data['ret']));
			}	
			// var_dump(count($data['ret']));
			if(count($data['ret'])!=0){
				// 教学水平最高分			
				$where=" and 1=1";
				if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
					$where.=' and db_attend.listen_time>'.strtotime($_POST['start_time'].' 00:00:00').' and db_attend.listen_time<'.strtotime($_POST['end_time']." 23:59:59");
				}
				$sql=$this->sqlEscape("select * from db_attend where teaching_level_score=(SELECT max(teaching_level_score) from db_attend where status=?? ".$where." ) and status=?? ".$where." group by teacher_name",array(AttendStatus::Submit[XPHEnum::Value],AttendStatus::Submit[XPHEnum::Value]));
				$max=$this->Attend_model->query($sql);			
				$max_score=$max[0]['teaching_level_score'];
				$max_teacher_name=implode(',', array_column($max,'teacher_name'));
				$max_level=sprintf("%.2f",$max_score).'('.$max_teacher_name.')';
				//教学水平 最低分
				$where=" and  1=1";
				if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
					$where.=' and db_attend.listen_time>'.strtotime($_POST['start_time'].' 00:00:00').' and db_attend.listen_time<'.strtotime($_POST['end_time']." 23:59:59");
				}
				$sql=$this->sqlEscape("select * from db_attend where teaching_level_score=(SELECT min(teaching_level_score) from db_attend where status=?? ".$where." ) and status=?? ".$where." group by teacher_name",array(AttendStatus::Submit[XPHEnum::Value],AttendStatus::Submit[XPHEnum::Value]));
				$min=$this->Attend_model->query($sql);			
				$min_score=$min[0]['teaching_level_score'];
				$min_teacher_name=implode(',', array_column($min,'teacher_name'));
				$min_level=sprintf("%.2f",$min_score).'('.$min_teacher_name.')';

				// 教学效果高分			
				$where=" and  1=1";
				if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
					$where.=' and db_attend.listen_time>'.strtotime($_POST['start_time'].' 00:00:00').' and db_attend.listen_time<'.strtotime($_POST['end_time']." 23:59:59");
				}
				$sql=$this->sqlEscape("select * from db_attend where teaching_effect_score=(SELECT max(teaching_effect_score) from db_attend where status=?? ".$where." ) and status=?? ".$where." group by teacher_name",array(AttendStatus::Submit[XPHEnum::Value],AttendStatus::Submit[XPHEnum::Value]));
				$max=$this->Attend_model->query($sql);			
				$max_score=$max[0]['teaching_effect_score'];
				$max_teacher_name=implode(',', array_column($max,'teacher_name'));
				$max_effect=sprintf("%.2f",$max_score).'('.$max_teacher_name.')';
				//教学效果最低分
				$where=" and  1=1";
				if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
					$where.=' and db_attend.listen_time>'.strtotime($_POST['start_time'].' 00:00:00').' and db_attend.listen_time<'.strtotime($_POST['end_time']." 23:59:59");
				}
				$sql=$this->sqlEscape("select * from db_attend where teaching_effect_score=(SELECT min(teaching_effect_score) from db_attend where status=?? ".$where." ) and status=?? ".$where." group by teacher_name",array(AttendStatus::Submit[XPHEnum::Value],AttendStatus::Submit[XPHEnum::Value]));
				$min=$this->Attend_model->query($sql);	
				$min_score=$min[0]['teaching_effect_score'];
				$min_teacher_name=implode(',', array_column($min,'teacher_name'));
				$min_effect=sprintf("%.2f",$min_score).'('.$min_teacher_name.')';

				array_push($data['ret'],array('title'=>'合计',''=>'','attend_num_unit'=>$attend_num_unit,'attend_count_unit'=>$attend_count_unit,'attend_num_school'=>$attend_num_school,'attend_count_school'=>$attend_count_school,'avg_level_count'=>$avg_level_count,'avg_effect_count'=>$avg_effect_count,'abovenine_level_count'=>$abovenine_level_count,'aboveeight_level_count'=>$aboveeight_level_count,'abovenine_effect_count'=>$abovenine_effect_count,'aboveeight_effect_count'=>$aboveeight_effect_count,'max_level'=>$max_level,'min_level'=>$min_level,'max_effect'=>$max_effect,'min_effect'=>$min_effect));
			}else{
				array_push($data['ret'],array('title'=>'合计',''=>'','attend_num_unit'=>'','attend_count_unit'=>'','attend_num_school'=>'','attend_count_school'=>'','avg_level_count'=>'','avg_effect_count'=>'','abovenine_level_count'=>'','aboveeight_level_count'=>'','abovenine_effect_count'=>'','aboveeight_effect_count'=>'','max_level'=>'','min_level'=>'','max_effect'=>'','min_effect'=>''));
			}
			

			$this-> load ->library('PHPExcel/PHPExcel');
			$excel = new PHPExcel();
	        //公共样式
			$excel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(11); 
			$excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			$excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setWrapText(true);
			$excel->getActiveSheet()->getDefaultColumnDimension()->setWidth(23);
			$excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
			$excel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);

			$x_line=0;
			$y_line=1;
			
			//标题
			$title=array($_POST['start_time'].'至'.$_POST['end_time'],'开课单位','督导听课人数','督导听课次数','校督导听课人数','校督导听课次数','教学水平平均分','教学效果平均分','教学水平90分(含)以上','教学水平80以下','教学效果90分(含)以上','教学效果80以下','教学水平最高分','教学水平最低分','教学效果最高分','教学效果最低分');
			foreach ($title as $k => $v) {
				$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line,$v);
				$excel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line)->getFont()->setBold(true);   
				$x_line++;
			}

			$x_line=0;
			$y_line=2;
	        //正式数据
			foreach ($data['ret'] as $k => $v) {        	
				foreach ($v as $k1 => $v1) {
					if($k1=='listen_id'){
						continue;
					}
					$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line,$v1);


					$x_line++;
				}
				$x_line=0;
				$y_line++;
			}
			$write = new PHPExcel_Writer_Excel2007($excel); 
			$director = "upload/export/" . date('Ymd', time()) . "/";
			if (!file_exists($director)) {
	            //检查是否有该文件夹，如果没有就创建，并给予最高权限
				mkdir($director, 0777);
			}        
			$file_name ='督导听课汇总'.date('Ymd').'.xlsx';       	
			$write->save($director.iconv('utf-8', 'gb2312', $file_name));
			exit(retJson('返回地址',true,$director.$file_name));
		}
	}

	// 获取被听课教师列表
	/**
	 * @param course_units 开课单位
	 * @param start_time 开始时间
	 * @param end_time 结束时间
	 */
	function getTeacherList($type,$course_units,$source,$start_time=false,$end_time=false,$select='',$isExport=false){

		if($isExport){//是否导出
			//$select = $_POST['select'];
			$type = $_POST['type'];
			$course_units = $_POST['course_units'];
			$start_time = $_POST['start_time'];
			$end_time = $_POST['end_time'];
		}


		$course_units=urldecode($course_units);
		if($start_time){
			$start_time=strtotime(urldecode($start_time)) ;
		}
		if($end_time){
			$end_time=strtotime(urldecode($end_time));
		}	

		
		$connar['where']=$this->sqlEscape("status=?? and course_units=?? ",array(AttendStatus::Submit[XPHEnum::Value],$course_units));
		$connar['orderby']="teacher_name";
		// $connar['join']=array('db_admin','db_admin.id=db_')
		if($type==1){
			$connar['items']="teacher_name,teacher_code,score";	
			$connar['where'].=$this->sqlEscape("and type=??",array(AttendType::Unit[XPHEnum::Value]));
		}else if($type==2){
			$connar['items']="teacher_name,teacher_code,score";	
			$connar['where'].=$this->sqlEscape("and type=??",array(AttendType::School[XPHEnum::Value]));
		}else if($type==3){
			$connar['where'].=$this->sqlEscape("and teaching_level_score>=90",array());
		}else if($type==4){
			$connar['where'].=$this->sqlEscape("and teaching_level_score<80",array());
		}else if($type==5){
			$connar['where'].=$this->sqlEscape("and teaching_effect_score>=90",array());
		}else if($type==6){
			$connar['where'].=$this->sqlEscape("and teaching_effect_score<80",array());
		}
		// if (isset($params['start_time']) && isset($params['end_time'])) {
		// 	$where.=$this->sqlEscape(' and db_attend.listen_time>??  and db_attend.listen_time<??',array(strtotime($params['start_time'].' 00:00:00'),strtotime($_POST['end_time']." 23:59:59")));
		// }else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
		// 	$where.=' and db_attend.listen_time>'.strtotime($_POST['start_time'].' 00:00:00').' and db_attend.listen_time<'.strtotime($_POST['end_time']." 23:59:59");
		// }
		if($start_time){
			$connar['where'].=$this->sqlEscape(' and listen_time>??',array($start_time.' 00:00:00'));
		}
		if($end_time){
			$connar['where'].=$this->sqlEscape(' and listen_time<??',array($end_time." 23:59:59"));
		}
		$data['list']=$this->Attend_model->get_list_full($connar);


	

		if($type==1){
			$data['title']="督导听课人次";
		}else if($type==2){
			$data['title']="校督导听课人次";
		}else if($type==3){
			$data['title']="教学水平90分(含)以上";
		}else if($type==4){
			$data['title']="教学水平80以下";
		}else if($type==5){
			$data['title']="教学效果90分(含)以上";
		}else if($type==6){
			$data['title']="教学效果80以下";
		}
		//导出
	   if($isExport){

		   $this-> load ->library('PHPExcel/PHPExcel');
            $excel = new PHPExcel();
            //公共样式
            $excel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(11); 
            $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setWrapText(true);
            $excel->getActiveSheet()->getDefaultColumnDimension()->setWidth(23);
            $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $excel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);

            $x_line=0;
            $y_line=1;

            //正式开始
            $excel = new PHPExcel();
            $excel->getActiveSheet()->getDefaultColumnDimension()->setWidth(15);

              //标题
            $excel->getActiveSheet()->mergeCells(PHPExcel_Cell::stringFromColumnIndex(0).'1:'.PHPExcel_Cell::stringFromColumnIndex(4).'1');
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(0).'1',$data['title']);
            $excel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $excel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
            $excel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16);
            $excel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $i=2;
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(0).$i,'序号');
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(1).$i,'工号');
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(2).$i,'姓名');
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(3).$i,'总分');
              
        $i++;
        foreach ($data['list'] as $k => $v) {
           
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(0).$i,$k+1);
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(1).$i,$v['teacher_code']);
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(2).$i,$v['teacher_name']); 
            $excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex(3).$i,$v['score']);           
            $i++;
        }
        //设置对齐
        $excel->getActiveSheet()->getStyle('A1:'.PHPExcel_Cell::stringFromColumnIndex(9).($i-1))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $excel->getActiveSheet()->getStyle('A1:'.PHPExcel_Cell::stringFromColumnIndex(0).($i-1))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


            $write = new PHPExcel_Writer_Excel2007($excel); 
            $director = "upload/export/" . date('Ymd', time()) . "/";
            if (!file_exists($director)) {
                //检查是否有该文件夹，如果没有就创建，并给予最高权限
                mkdir($director, 0777);
            }        
            $file_name =$data['title'].date('Ymd').'.xlsx';           
            $write->save($director.iconv('utf-8', 'gb2312', $file_name));
            exit(retJson('返回地址',true,$director.$file_name));

	   }else{
	  	 	$data['params'] = array('select'=>$select,'type'=>$type,'source'=>$source,'course_units'=>$course_units,'start_time'=>$start_time,'end_time'=>$end_time);
	   	    $this->load->view('admin/attendDept',$data);
	   }

	}
	
}



